﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Backend;
using System.Threading.Tasks;

namespace Data_Layer
{
    public class DBUsers : IDBUsers
    {
        SqlConnection con;
        List<Users> allUsers = new List<Users>();

        public DBUsers()
        {
            con = Connection.GetInstance().GetConnection();
        }

        public void createUser(Users user)
        {
            try  
            {
                con.Open();
                String Query = "use Internship insert into Users values(" + "'" + user.UserName + "' , '" + user.PassWord + "'," + user.Balance + ")";
                System.Console.WriteLine(Query);
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();                
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with createUser()");
                Console.WriteLine(ex.Message);
            }

            finally
            {
                con.Close();
            }
        }

        public void deleteUser(string username)
        {
            try
            {
                con.Open();
                String Query = "use Internship delete from Users where username = " + "'" + username + "'";
                System.Console.WriteLine(Query);
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();                
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with deleteUser()");
                Console.WriteLine(ex.Message);
            }

            finally
            {
                con.Close();
            }

        }

        public void updateUser(Users user)
        {
            try
            {
                con.Open();
                String Query = "use Internship update Users set " + "password=" + "'" + user.PassWord + "', " + "balance=" + user.Balance + " where username = " + "'" + user.UserName + "'";
                System.Console.WriteLine(Query);
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();                
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with updateUser()");
                Console.WriteLine(ex.Message);
            }

            finally
            {
                con.Close();
            }

        }

        public void updateUserbalance(string username, int balance)
        {
            try
            {
                con.Open();
                String Query = "use Internship update Users set balance= " + balance + " where username = " + "'" + username + "'";
                System.Console.WriteLine(Query);
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with updateUserbalance()");
                Console.WriteLine(ex.Message);
            }

            finally
            {
                con.Close();
            }
        }

        public Users readUser(string username)
        {
            try
            {
                con.Open();
                Users user = new Users();
                String Query = "use Internship select * from Users where username =" + "'" + username + "'";
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();

                while (reader.Read())
                {
                    user.UserName = (string)reader["username"];
                    user.PassWord = (string)reader["password"];
                    user.Balance = (int)reader["balance"];
                }

                reader.Close();
                return user;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with readUser()");
                Console.WriteLine(ex.Message);

                return null;

            }

            finally
            {
                con.Close();
            }
        }

        public List<Users> getallUsers()
        {
            try
            {
                con.Open();
                String Query = "use Internship select * from Users";
                System.Console.WriteLine(Query);
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    Users user = new Users();
                    user.UserName = (string)reader["username"];
                    user.PassWord = (string)reader["password"];
                    user.Balance = (int)reader["balance"];
                    
                    allUsers.Add(user);
                }

                reader.Close();

                return allUsers;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with getallUsers()");
                Console.WriteLine(ex.Message);

                return allUsers;
            }

            finally
            {
                con.Close();
            }
        }
         
    }
}
